package DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import JDBC.DATABASE;
import VO.Ground;

public class GroundDao {

	
	public ArrayList queryAllGround(){	//查询所有球场信息
		ArrayList ground = new ArrayList();
		Connection conn=DATABASE.getConnection();
		String sql="select * from GROUND order by gid";
		ResultSet rs = DATABASE.query(sql);
		try {
			while(rs.next()){
				System.out.print("11");
				Ground grounds = new Ground();
				grounds.setGid(rs.getInt("GID"));
				grounds.setName(rs.getString("NAME"));
				grounds.setState(rs.getString("STATE"));
				grounds.setTypeid(rs.getInt("TYPEID"));
				ground.add(grounds);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
		}catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			DATABASE.close(conn);
		}
		return ground;
	}
	/*
	 * 保存球场信息
	 */
	public void saveGround(Ground ground){
		Connection conn=DATABASE.getConnection();
		String sql=" INSERT INTO GROUND VALUES (?,?,?,?)";
		try{
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1,ground.getGid());
			ps.setString(2,ground.getName());
			ps.setString(3,ground.getState());
			ps.setInt(4,ground.getTypeid());
			ps.executeUpdate();
			ps.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DATABASE.close(conn);
		}		
	}
	/*
	 * 根据编号删除球场信息
	 */
	
	public void deleteGround(String gid){
		Connection conn=DATABASE.getConnection();
		String sql ="delete  from GROUND where gid=? ";
  
		try{
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setString(1,gid);
			ps.executeUpdate();
			ps.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DATABASE.close(conn);
		}

	}
	/*
	 * 根据类型查询各类型球场数量
	 */
	
	public int groundNum(int typeid){
		int g_num=0;
		Connection conn=DATABASE.getConnection();

		try{
			CallableStatement cs=conn.prepareCall("{?=call ground_num(?)}"); 
			//调用球场总数目的函数ground_num
			cs.registerOutParameter(1,Types.INTEGER); //要输出的值为整型 
			cs.setInt(2,typeid);       
			cs.execute(); 
			System.out.println("2");
			g_num=cs.getInt(1);
			System.out.print(g_num);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DATABASE.close(conn);
		}
		return g_num;
	}
	
	

}
